Page 32 of 44
Question Identification and Explanations
Scenario: Vehicle Trade-Ins
Query 1: How many vehicles qualify for trade in through the municipal lease program? Select all vehicles
with 5,000 miles or greater and are more than 3 years old. Show how many distinct types of vehicles
apply, giving the make, model and type in the result. Order the results descending by mileage and give a
count of how many vehicles are within each output row and percentage of the overall fleet.
An administrator of the vehicle fleet wants information regarding how many vehicles are eligible to be
traded in for new ones per a lease agreement. A municipality can enter a lease agreement with a vehicle
supplier to prevent the average age of its vehicles from getting too old. As a vehicle gets older, it often
takes more time, parts, fluids, and money to maintain. To prevent the ‘nickel and dime’ effect, it is
advantageous to have a replacement plan to save on maintenance costs. Moreover, knowing the make
and model of the aging vehicles within the fleet will help the administrator identify purchasing trends.
Knowing the percentage of vehicles that are tradeable out of the whole allows the administrator to
accurately plan how many replacement leases they should schedule in the next few budget years. Type of
vehicle is important since ‘CDL Vehicles’ are usually more expensive to replace given their size, capacity
and special registration.
Scenario: Ordering Parts from a Vendor
Query 2: Generate a list of parts that have a quantity 10 ‘units’ or under. List them in a results table
alongside the vendor which sells the part, their phone number, email if known and agent if known. Due to
intra-state shipping surcharges, administration doesn’t want anyone purchasing parts from out-of-state
(PA in my case). Disqualify any parts from the list which have vendors outside of PA.
The in-house mechanic wants to stock the parts room for the month and would like to replenish materials
that are running low in the shop. This is a standard supply ordering activity that can happen per week or
per month in a mechanic’s shop. Its very useful to know not only what you are running low on, but the
right contacts to order the supplies from. I have personally seen a purchasing freeze similar to this
scenario, and everyone can relate to delivery surcharges related to fuel cost increases.
Scenario: Monthly Inspection List
Query 3: Generate a list of how many vehicle inspections are due in which months. There should be 12
results, one for each month with the number of inspections given in each month, even if there are none.
The list should be chronologically correct in order of month. CDL Vehicle inspections run on a 6-month
interval, where Standard Vehicle inspections run on a 12-month interval.
The in-house mechanic runs a report at the beginning of the year which forecasts how many vehicle
inspections are due and on what month they land. This is a workload query to judge proper time
management in forecasting what inspections land where. As inspections can be shifted within a 3-month
window prior to the deadline, the mechanic can use this report to balance out the workload on the books
if needed.